package dao.impl;

import dao.StaffInfoDao;
import entity.StaffInfo;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import util.DBConn;
import util.DruidUtil;

import java.sql.SQLException;
import java.util.List;

/**
 * @author gwf
 * @date 2025-02-25
 */
public class StaffInfoDaoImpl4DbUtils implements StaffInfoDao {
    QueryRunner qr = new QueryRunner(DruidUtil.getDataSource());
    @Override
    public List<StaffInfo> list() {
        String sql = "select id,name,phone,sta_pos as staPos from staffs ";

        BeanListHandler<StaffInfo> beanListHandler = new BeanListHandler<>(StaffInfo.class);
        try {
            return qr.query(sql,beanListHandler);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public StaffInfo one(int id) {
        String sql = "select name,age,phone,sta_pos as staPos,add_time as addTime,update_time as updateTime from staffs where id=?";

        BeanHandler<StaffInfo> beanHandler = new BeanHandler<>(StaffInfo.class);

        try {
            return qr.query(sql,beanHandler,id);
        } catch (SQLException e) {
            throw new RuntimeException("根据id查询异常",e);
        }
    }

    @Override
    public int add(StaffInfo staffInfo) {
        //第一:定义要操作的SQL语句
        String sql = "insert into staffs(name,age,phone,sta_pos) values(?,?,?,?)";

        //第三：执行 SQL 语句
        Object[] params = {staffInfo.getName(), staffInfo.getAge(), staffInfo.getPhone(), staffInfo.getStaPos()};
        try {
            return qr.update(sql,params);
        } catch (SQLException e) {
            throw new RuntimeException("添加失败",e);
        }
    }

    /**
     * 利用Druid 工具类执行SQL语句
     * @param id 员工编号
     * @return
     */
    @Override
    public int del(int id) {
        String sql = "delete from staffs where id=?";
        Object[] params = {id};

        try {
            return qr.update(sql,params);
        } catch (SQLException e) {
            throw new RuntimeException("删除失败",e);
        }
    }

    @Override
    public int modify(StaffInfo staffInfo) {
        String sql = "update staffs set name=?,age=?,phone=?,sta_pos=? where id = ?";

        Object[] params = {staffInfo.getName(), staffInfo.getAge(), staffInfo.getPhone(), staffInfo.getStaPos(), staffInfo.getId()};

        try {
            return qr.update(sql,params);
        } catch (SQLException e) {
            throw new RuntimeException("修改失败", e);
        }
    }

    @Override
    public List<StaffInfo> selectPage(int currentPage, int pageSize)  {
        //注意：实体对象的属性名称和数据表的字段名称必须一致
        //否则查询数据不自动映射
        //解决：通过别名指定实体对象的属性名称
        String sql = "select id,name,phone,sta_pos as staPos from staffs limit ?,?";

        int limit = (currentPage-1)*pageSize;
        BeanListHandler<StaffInfo> beanListHandler = new BeanListHandler<>(StaffInfo.class);
        try {
            //ResultSetHandler
            //执行查询，在query方法内部通过调用rsh.handle(resultSet)方法,
            //实现结果集转换为指定类型
            return qr.query(sql,beanListHandler,limit, pageSize);
        } catch (SQLException e) {
            throw new RuntimeException("分页查询异常",e);
        }
    }
}
